package _2工具类;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class WorldDAOImpl implements IWorldDAO {
	
	Connection conn;
	Statement stmt;
	public void getconnection() throws SQLException
	{
		// 1. 获取连接
		 conn = DbUtils1.getConnection();
		// 2. 查询对象
		 stmt = conn.createStatement();
	}
	//* 查询所有国家
	@Override
	public List<Country> findAllCountry() throws SQLException {
		// TODO Auto-generated method stub
		List<Country> result = new ArrayList<>();
		getconnection();
		// 3. 执行查询
		String sql = "select * from country";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next())
		{
			Country c = new Country(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
			result.add(c);
		}
		// 4. 释放资源
		DbUtils.destoryResource(conn, stmt, rs);		
		return result;
	}
	//* 查询所有城市
	@Override
	public List<City> findAllCity() throws SQLException {
		// TODO Auto-generated method stub
		List<City> result = new ArrayList<>();
		getconnection();
		// 3. 执行查询
		String sql = "select * from city";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next())
		{
			City c = new City(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),rs.getInt(5));
			result.add(c);
		}
		// 4. 释放资源
		DbUtils.destoryResource(conn, stmt, rs);		
		return result;
	}
	//* 根据国家名称模糊查询国家
	@Override
	public List<Country> findCountry(String countryname) throws SQLException {
		// TODO Auto-generated method stub
		List<Country> result = new ArrayList<>();
		getconnection();
		// 3. 执行查询
		String sql = "select * from country where Name like '"+countryname+"%'";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next())
		{
			Country c = new Country(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
			result.add(c);
		}
		// 4. 释放资源
		DbUtils.destoryResource(conn, stmt, rs);		
		return result;
	}
	// * 根据城市名称查询城市
	@Override
	public City findCity(String cityname) throws SQLException {
		// TODO Auto-generated method stub
		getconnection();
		// 3. 执行查询
		String sql = "select * from city where Name='"+cityname+"'";
		ResultSet rs = stmt.executeQuery(sql);
		City result = null;
		if(rs.next())
		{
			result = new City(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),rs.getInt(5));
		}
		// 4. 释放资源
		DbUtils.destoryResource(conn, stmt, rs);		
		return result;
	}
	//* 根据国家名称查询这个国家有哪些城市
//	SELECT * from city WHERE CountryCode = (SELECT `Code` from country WHERE `Name` = 'Afghanistan')
	@Override
	public List<City> findCountryCity(String countryname) throws SQLException {
		// TODO Auto-generated method stub
		List<City> result = new ArrayList<>();
		getconnection();
		// 3. 执行查询
		String sql = "select * from city where CountryCode = ("+"select Code from country where Name = '"+countryname+"')";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next())
		{
			City c = new City(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),rs.getInt(5));
			result.add(c);
		}
		// 4. 释放资源
		DbUtils.destoryResource(conn, stmt, rs);		
		return result;
	}
	// * 查询某个国家的语言使用情况
	@Override
	public List<CountryLanguage> findLanguage(String countryname) throws SQLException {
		// TODO Auto-generated method stub
		List<CountryLanguage> result = new ArrayList<>();
		getconnection();
		// 3. 执行查询
		String sql = "select * from countrylanguage where CountryCode = ("+"select Code from country where Name = '"+countryname+"')";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next())
		{
			CountryLanguage c = new CountryLanguage(rs.getString(1),rs.getString(2),rs.getString(3),rs.getFloat(4));
			result.add(c);
		}
		// 4. 释放资源
		DbUtils.destoryResource(conn, stmt, rs);		
		return result;
	}

}
